/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.entity.PhongTro;
import model.service.DanhgiaDAOService;
import model.service.PhongTroDAOService;
import model.service.QuanDAOService;

import model.service.UsersDAOService;

/**
 *
 * @author duy
 */
public class PhongTroDAO implements PhongTroDAOService {

    private static PhongTroDAO phongtroDAO;

    QuanDAOService quanDAOService;
    UsersDAOService usersDAOService;
    DanhgiaDAOService danhgiaDAOService;

    public static PhongTroDAO getInstance() {
        if (phongtroDAO == null) {
            phongtroDAO = new PhongTroDAO();

        }
        return phongtroDAO;
    }

    public PhongTroDAO() {
        quanDAOService = QuanDAO.getInstance();
        usersDAOService = UserDAO.getInstance();
        danhgiaDAOService = DanhGiaDAO.getInstance();
    }

    // Lấy tất cả Phòng trọ trong ban phòng trọ;
    @Override
    public List<PhongTro> getAllPhong() {
        List<PhongTro> list = new ArrayList<>();
        try {
            Connection conn = ConnectionFactory.getConnection();
            String sql = "select * from tbl_phongtro";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                PhongTro item = new PhongTro();
                item.setMaphongtro(rs.getInt("maphongtro"));
                item.setTieudegioithieu(rs.getString("tieudegioithieu"));
                item.setTenchunha(rs.getString("tenchunha"));
                item.setSdt(rs.getInt("sdt"));
                item.setDiachi(rs.getString("diachi"));
                item.setSophong(rs.getInt("sophong"));
                item.setDientich(rs.getInt("dientich"));
                item.setGhichu(rs.getString("ghichu"));
                item.setNgaydang(rs.getDate("ngaydang"));
                item.setGiaphong(rs.getInt("giaphong"));
                item.setKinhdo(rs.getFloat("kinhdo"));
                item.setVido(rs.getFloat("vido"));
                item.setIsActive(rs.getBoolean("isActive"));;

                int maquan = rs.getInt("maquan");
                item.setQuan(quanDAOService.getQuanbyMa(maquan));

                int mauser = rs.getInt("mauser");
                item.setUser(usersDAOService.getusersbyID(mauser));

                int madanhgia = rs.getInt("madanhgia");
                item.setDanhgia(danhgiaDAOService.getDanhgiabyID(madanhgia));

                list.add(item);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return list;

    }

    @Override
    public List<PhongTro> getPhongTrobyQUan(int maquan) {
        List<PhongTro> list = new ArrayList<>();
        try {
            Connection conn = ConnectionFactory.getConnection();
            String sql = "select * from tbl_phongtro where maquan=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, maquan);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                PhongTro item = new PhongTro();
                item.setMaphongtro(rs.getInt("maphongtro"));
                item.setTieudegioithieu(rs.getString("tieudegioithieu"));
                item.setTenchunha(rs.getString("tenchunha"));
                item.setSdt(rs.getInt("sdt"));
                item.setDiachi(rs.getString("diachi"));
                item.setSophong(rs.getInt("sophong"));
                item.setDientich(rs.getInt("dientich"));
                item.setGhichu(rs.getString("ghichu"));
                item.setNgaydang(rs.getDate("ngaydang"));
                item.setKinhdo(rs.getFloat("kinhdo"));
                item.setVido(rs.getFloat("vido"));
                item.setIsActive(rs.getBoolean("isActive"));;

                int mauser = rs.getInt("mauser");
                item.setUser(usersDAOService.getusersbyID(mauser));

//                int madanhgia = rs.getInt("madanhgia");
//                item.setDanhgia(danhgiaDAOService.getDanhgiabyID(madanhgia));
                list.add(item);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public PhongTro getThongtinPTbyMaPhongTro(int maphongtro) {
        PhongTro item = new PhongTro();
        try {
            Connection conn = ConnectionFactory.getConnection();
            String sql = "select * from tbl_phongtro where maphongtro=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, maphongtro);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                item.setMaphongtro(rs.getInt("maphongtro"));
                item.setTieudegioithieu(rs.getString("tieudegioithieu"));
                item.setTenchunha(rs.getString("tenchunha"));
                item.setSdt(rs.getInt("sdt"));
                item.setDiachi(rs.getString("diachi"));
                item.setSophong(rs.getInt("sophong"));
                item.setDientich(rs.getInt("dientich"));
                item.setGhichu(rs.getString("ghichu"));
                item.setNgaydang(rs.getDate("ngaydang"));
                item.setKinhdo(rs.getFloat("kinhdo"));
                item.setVido(rs.getFloat("vido"));
                item.setIsActive(rs.getBoolean("isActive"));;

                int maquan = rs.getInt("maquan");
                item.setQuan(quanDAOService.getQuanbyMa(maquan));

                maquan = rs.getInt("mauser");
                item.setUser(usersDAOService.getusersbyID(maquan));

                int madanhgia = rs.getInt("madanhgia");
                item.setDanhgia(danhgiaDAOService.getDanhgiabyID(madanhgia));
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return item;
    }

    @Override
    public boolean createPhongTro(PhongTro phongTro) {
        boolean isCheck = false;
        try {
            Connection conn = ConnectionFactory.getConnection();//thieu dau phay cho nay
            String sql = "insert into tbl_phongtro(maphongtro,tieudegioithieu, tenchunha, sdt, diachi,"
                    + " sophong, dientich,ghichu,ngaydang,giaphong,kinhdo,vido,isActive,mauser,maquan,madanhgia) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, phongTro.getMaphongtro());
            pstmt.setString(2, phongTro.getTieudegioithieu());
            pstmt.setString(3, phongTro.getTenchunha());
            pstmt.setInt(4, phongTro.getSdt());
            pstmt.setString(5, phongTro.getDiachi());
            pstmt.setInt(6, phongTro.getSophong());
            pstmt.setInt(7, phongTro.getDientich());
            pstmt.setString(8, phongTro.getGhichu());
            pstmt.setDate(9, (Date) phongTro.getNgaydang());
            pstmt.setInt(10, phongTro.getGiaphong());
            pstmt.setFloat(11, phongTro.getKinhdo());
            pstmt.setFloat(12, phongTro.getVido());
            pstmt.setBoolean(13, phongTro.isIsActive());
            pstmt.setInt(14, phongTro.getUser().getMauser());
            pstmt.setInt(15, phongTro.getQuan().getMaquan());
            pstmt.setInt(16, phongTro.getDanhgia().getMadanhgia());

            pstmt.executeUpdate();
            isCheck = true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return isCheck;
    }

    @Override
    public boolean xoa(int madanhgia) throws Exception {
        boolean isCheck = false;
        try {
            Connection conn = ConnectionFactory.getConnection();
            String sql = "delete from tbl_danhgia where madanhgia =?";
            PreparedStatement sm = conn.prepareStatement(sql);
            sm.setInt(1, madanhgia);
            sm.executeUpdate();

            sql = "delete from tbl_phongtro where madanhgia =?";
            sm = conn.prepareStatement(sql);
            sm.setInt(1, madanhgia);
            sm.executeUpdate();

            isCheck = true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return isCheck;
    }

    @Override
    public boolean editPhongTro(PhongTro phongTro) {
        boolean isCheck = false;
        try {
            Connection conn = ConnectionFactory.getConnection();//thieu dau phay cho nay
            String sql = "update tbl_phongtro set tieudegioithieu=?,tenchunha=?,sdt=?,diachi=?,sophong=?,dientich=?,ghichu=?,"
                    + "ngaydang=?,giaphong=?,kinhdo=?,vido=?,isActive=?,maquan=? where maphongtro = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            
            pstmt.setString(1, phongTro.getTieudegioithieu());
            pstmt.setString(2, phongTro.getTenchunha());
            pstmt.setInt(3, phongTro.getSdt());
            pstmt.setString(4, phongTro.getDiachi());
            pstmt.setInt(5, phongTro.getSophong());
            pstmt.setInt(6, phongTro.getDientich());
            pstmt.setString(7, phongTro.getGhichu());
            pstmt.setDate(8, (Date) phongTro.getNgaydang());
            pstmt.setInt(9, phongTro.getGiaphong());
            pstmt.setFloat(10, phongTro.getKinhdo());
            pstmt.setFloat(11, phongTro.getVido());
            pstmt.setBoolean(12, phongTro.isIsActive());
             pstmt.setBoolean(12, phongTro.isIsActive());
              pstmt.setInt(13, phongTro.getQuan().getMaquan());
            pstmt.setInt(14, phongTro.getMaphongtro());
            pstmt.executeUpdate();
            isCheck = true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return isCheck;
    }

    @Override
    public boolean update_danhgia(int madanhgia, float diemdanhgia, int songuoidanhgia, String diachiMAC) {
        boolean isCheck = false;
        try {
            Connection conn = ConnectionFactory.getConnection();//thieu dau phay cho nay
            String sql = "update tbl_danhgia set diemdanhgia=?,songuoidanhgia=?,diachiMAC=? where madanhgia = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setFloat(1, diemdanhgia);
            pstmt.setInt(2, songuoidanhgia);
            pstmt.setString(3, diachiMAC);
            pstmt.setInt(4, madanhgia);
            pstmt.executeUpdate();
            isCheck = true;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return isCheck;
    }

}
